package com.narbeshuber.tablet.lasalara.dbmanager;

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.narbeshuber.tablet.lasalara.model.Book;
import com.narbeshuber.tablet.lasalara.model.Chapter;
import com.narbeshuber.tablet.lasalara.model.Question;

public class DBHelper {

	private static final String BOOK_TABLE = "books";
	private static final String CHAPTER_TABLE = "chapters";
	private static final String QUESTION_TABLE = "questions";

	private enum BOOK_FIELDS {
		ID("_id"), 
		BOOK_TITLE("title"), 
		BOOK_AUTHOR_NAME("name"), 
		BOOK_AUTHOR_EMAIL("email"), 
		IS_DOCTOR("doctor"), 
		INSTITUTION("institution");

		private String name;

		private BOOK_FIELDS(String name) {
			this.name = name;
		}

		public String getName() {
			return this.name;
		}
	};

	private enum CHAPTER_FIELDS {
		ID("_id"), 
		BOOK_KEY("book_key"), 
		CHAPTER_TITLE("title"),
		CHAPTER_AUTHOR_NAME("name"), 
		CHAPTER_AUTHOR_EMAIL("email"), 
		IS_DOCTOR("dr"), 
		INSTITUTION("institution"), 
		CHAPTER_VERSION("version"),
		ALLOW_PROP("allow_prop");
		private String name;

		private CHAPTER_FIELDS(String name) {
			this.name = name;
		}

		public String getName() {
			return this.name;
		}
	};

	private enum QUESTION_FIELDS {
		ID("_id"), 
		CHAPTER_KEY("chapter_key"), 
		QUESTION("question"), 
		ANSWER("answer"), 
		TIMES_ANSWERED("times_answered"), 
		SHOW_DATE("show_date");

		private String name;

		private QUESTION_FIELDS(String name) {
			this.name = name;
		}

		public String getName() {
			return this.name;
		}
	};

	private static final String DATABASE_NAME = "lasalara";
	private static final String FULL_PATH = "/data/data/com.narbeshuber.tablet.lasalara/databases/lasalara";
	private static final int DATABASE_VERSION = 1;

	private Context context;
	private SQLiteDatabase database;

	public DBHelper(Context context) {
		this.context = context;
	}

	public void createDatabase() {
		OpenHelper openHelper = new OpenHelper(context);
		database = openHelper.getWritableDatabase();
	}

	public void deleteDatabase() {
		context.deleteDatabase(DATABASE_NAME);
	}

	public void beginTransaction() {
		database.beginTransaction();
	}

	public void endTransaction() {
		database.setTransactionSuccessful();
		database.endTransaction();
	}
	
	// ------------------------------------------------------------------ Books

	public void insertBook(Book book) {
		String sql = "insert into " + BOOK_TABLE + " ("
				+ BOOK_FIELDS.ID.getName() + ", "
				+ BOOK_FIELDS.BOOK_TITLE.getName() + ","
				+ BOOK_FIELDS.BOOK_AUTHOR_NAME.getName() + ", "
				+ BOOK_FIELDS.BOOK_AUTHOR_EMAIL.getName() + ", "
				+ BOOK_FIELDS.IS_DOCTOR.getName() + ", "
				+ BOOK_FIELDS.INSTITUTION.getName() + ") " + "values ('"
				+ book.getKey() + "',"
				+ DatabaseUtils.sqlEscapeString(book.getTitle()) + ",'"
				+ book.getAuthorName() + "','" + book.getAuthorEmail() + "','"
				+ book.getDr() + "','" + book.getInstitution() + "');";
		database.execSQL(sql);
	}
	
	public List<Book> getAllBooks() {
		List<Book> books = new ArrayList<Book>();
		Cursor cur = database.query(BOOK_TABLE, null, null, null, null, null,
				null);
		if (cur.moveToFirst()) {
			do {
				Book book = new Book();
				book.setKey(cur.getString(BOOK_FIELDS.ID.ordinal()));
				book.setAuthorEmail(cur.getString(BOOK_FIELDS.BOOK_AUTHOR_EMAIL
						.ordinal()));
				book.setAuthorName(cur.getString(BOOK_FIELDS.BOOK_AUTHOR_NAME
						.ordinal()));
				book.setDr(cur.getString(BOOK_FIELDS.IS_DOCTOR.ordinal()));
				book.setInstitution(cur.getString(BOOK_FIELDS.INSTITUTION
						.ordinal()));
				book.setTitle(cur.getString(BOOK_FIELDS.BOOK_TITLE.ordinal()));
				books.add(book);
			} while (cur.moveToNext());
		}
		cur.close();
		return books;
	}
	
	public void deleteBook(String bookKey) {
		String sql;

		for (Chapter chapter : getAllChaptersFromBook(bookKey)) {
			sql = "delete from " + QUESTION_TABLE + " where "
					+ QUESTION_FIELDS.CHAPTER_KEY.getName() + " = '"
					+ chapter.getChapterKey() + "';";

			database.execSQL(sql);
		}

		sql = "delete from " + CHAPTER_TABLE + " where "
				+ CHAPTER_FIELDS.BOOK_KEY.getName() + " = '" + bookKey + "';";

		database.execSQL(sql);

		sql = "delete from " + BOOK_TABLE + " where "
				+ BOOK_FIELDS.ID.getName() + " = '" + bookKey + "';";

		database.execSQL(sql);
	}
	
	// ------------------------------------------------------------------ Chapters
	
	public void insertChapter(Chapter chapter) {
		String sql = "insert into " + CHAPTER_TABLE + " ("
				+ CHAPTER_FIELDS.ID.getName() + ", "
				+ CHAPTER_FIELDS.BOOK_KEY.getName() + ","
				+ CHAPTER_FIELDS.CHAPTER_TITLE.getName() + ", "
				+ CHAPTER_FIELDS.CHAPTER_AUTHOR_NAME.getName() + ", "
				+ CHAPTER_FIELDS.CHAPTER_AUTHOR_EMAIL.getName() + ", "
				+ CHAPTER_FIELDS.IS_DOCTOR.getName() + ", "
				+ CHAPTER_FIELDS.INSTITUTION.getName() + ", "
				+ CHAPTER_FIELDS.CHAPTER_VERSION.getName() + ", "
				+ CHAPTER_FIELDS.ALLOW_PROP.getName() + ") " + "values ('"
				+ chapter.getChapterKey() + "','" 
				+ chapter.getBookKey() + "',"
				+ DatabaseUtils.sqlEscapeString(chapter.getTitle()) + ",'"
				+ chapter.getAuthorName() + "','"
				+ chapter.getAuthorEmail() + "','" 
				+ chapter.getDr() + "','"
				+ chapter.getInstitution() + "','"
				+ chapter.getVersion() + "','"
				+ chapter.getAllowProp() + "');";
		database.execSQL(sql);
	}
	
	public List<Chapter> getAllChaptersFromBook(String bookKey) {
		List<Chapter> chapters = new ArrayList<Chapter>();
		Cursor cur = database.query(CHAPTER_TABLE, null,
				CHAPTER_FIELDS.BOOK_KEY.getName() + "='" + bookKey + "'", null,
				null, null, null);
		if (cur.moveToFirst()) {
			do {
				Chapter chapter = new Chapter();
				chapter.setChapterKey(cur.getString(CHAPTER_FIELDS.ID.ordinal()));
				chapter.setBookKey(cur.getString(CHAPTER_FIELDS.BOOK_KEY
						.ordinal()));
				chapter.setTitle(cur.getString(CHAPTER_FIELDS.CHAPTER_TITLE
						.ordinal()));
				chapter.setAuthorEmail(cur
						.getString(CHAPTER_FIELDS.CHAPTER_AUTHOR_EMAIL
								.ordinal()));
				chapter.setAuthorName(cur
						.getString(CHAPTER_FIELDS.CHAPTER_AUTHOR_NAME.ordinal()));
				chapter.setDr(cur.getString(CHAPTER_FIELDS.IS_DOCTOR.ordinal()));
				chapter.setInstitution(cur.getString(CHAPTER_FIELDS.INSTITUTION
						.ordinal()));
				chapter.setVersion(cur.getString(CHAPTER_FIELDS.CHAPTER_VERSION
						.ordinal()));
				chapter.setAllowProp(cur.getString(CHAPTER_FIELDS.ALLOW_PROP
						.ordinal()));
				setCountsForChapter(chapter);
				chapters.add(chapter);
			} while (cur.moveToNext());
		}
		cur.close();
		return chapters;
	}
	
	public boolean isProposeAllowedInChapter(String chapterKey) {
		
		Cursor cur = database.query(CHAPTER_TABLE, null,
				CHAPTER_FIELDS.ID.getName() + "='" + chapterKey + "'", null,
				null, null, null);
		
		cur.moveToPosition(0);
		cur.getString(CHAPTER_FIELDS.ALLOW_PROP.ordinal());
		
		if (cur.getString(CHAPTER_FIELDS.ALLOW_PROP.ordinal()).equalsIgnoreCase("true"))
			return true;
		
		return false;
	}
	
	public void updateChapter(Chapter chapter) {
		String sql = "update "
				+ CHAPTER_TABLE + " set "
				+ CHAPTER_FIELDS.CHAPTER_TITLE.getName() + " = '"
				+ chapter.getTitle() + "', "
				+ CHAPTER_FIELDS.CHAPTER_AUTHOR_NAME.getName() + " = '"
				+ chapter.getAuthorName() + "', "
				+ CHAPTER_FIELDS.INSTITUTION.getName() + " = '"
				+ chapter.getInstitution() + "', "
				+ CHAPTER_FIELDS.IS_DOCTOR.getName() + " = '"
				+ chapter.getDr() + "', "
				+ CHAPTER_FIELDS.CHAPTER_VERSION.getName() + " = '"
				+ chapter.getVersion() + "', "
				+ CHAPTER_FIELDS.ALLOW_PROP.getName() + " = '"
				+ chapter.getAllowProp() + "' where "
				+ CHAPTER_FIELDS.ID.getName() + " = '"
				+ chapter.getChapterKey() + "';";
		
		database.execSQL(sql);
	}
	
	public void setCountsForChapter(Chapter chapter) {
		LinkedList<Question> questions = getAllQuestionsFromChapter(chapter
				.getChapterKey());
		int totalQuestions = questions.size();
		int answered = 0;
		for (Question question : questions) {
			if (question.getShowDate() != null
					&& question.getShowDate().after(new Date())) {
				answered++;
			}
		}
		chapter.setAnswered(answered);
		chapter.setTotalQuestions(totalQuestions);
	}
	
	public boolean isChapterInDB(String bookKey, String chapterKey) {
		List<Chapter> chapters = getAllChaptersFromBook(bookKey);

		for (Chapter chapter : chapters) {
			if (chapter.getChapterKey().equals(chapterKey))
				return true;
		}

		return false;
	}
	
	// ------------------------------------------------------------------ Questions
	
	public void insertQuestion(Question question) {
		String sql = "insert into "
				+ QUESTION_TABLE + " ("
				+ QUESTION_FIELDS.ID.getName() + ", "
				+ QUESTION_FIELDS.CHAPTER_KEY.getName() + ", "
				+ QUESTION_FIELDS.QUESTION.getName() + ","
				+ QUESTION_FIELDS.ANSWER.getName() + ", "
				+ QUESTION_FIELDS.TIMES_ANSWERED.getName() + ", "
				+ QUESTION_FIELDS.SHOW_DATE.getName() + ") "
				+ "values (NULL, '" 
				+ question.getChapterKey()+ "',"
				+ DatabaseUtils.sqlEscapeString(question.getQuestion())+ ","
				+ DatabaseUtils.sqlEscapeString(question.getAnswer())
				+ ",'" + question.getTimesAnswered()
				+ "',date(" + (question.getShowDate() != null ? question.getShowDate()
						.getTime() : null) + ",'unixepoch'));";
		database.execSQL(sql);
	}

	public void updateQuestion(Question question) {
		String sql = "update "
				+ QUESTION_TABLE + " set "
				+ QUESTION_FIELDS.TIMES_ANSWERED.getName() + " = "
				+ question.getTimesAnswered() + ", "
				+ QUESTION_FIELDS.SHOW_DATE.getName() + " = "
				+ (question.getShowDate() != null ? question.getShowDate()
						.getTime() : null) + " where "
				+ QUESTION_FIELDS.ID.getName() + " = '" + question.getId() + "';";
		database.execSQL(sql);
	}
	
	public void deleteQuestionsFromChapter(String chapterKey) {
		
		String sql = "delete from " + QUESTION_TABLE + " where "
				+ QUESTION_FIELDS.CHAPTER_KEY.getName() + " = '" + chapterKey + "';";

		database.execSQL(sql);
	}

	public LinkedList<Question> getAllQuestionsFromChapter(String chapterKey) {
		LinkedList<Question> questions = new LinkedList<Question>();
		Cursor cur = database
				.query(QUESTION_TABLE, null,
						QUESTION_FIELDS.CHAPTER_KEY.getName() + "='"
								+ chapterKey + "'", null, null, null, null);
		if (cur.moveToFirst()) {
			do {
				Question question = new Question();
				question.setId(cur.getInt(QUESTION_FIELDS.ID.ordinal()));
				question.setChapterKey(cur
						.getString(QUESTION_FIELDS.CHAPTER_KEY.ordinal()));
				question.setQuestion(cur.getString(QUESTION_FIELDS.QUESTION
						.ordinal()));
				question.setAnswer(cur.getString(QUESTION_FIELDS.ANSWER
						.ordinal()));
				question.setTimesAnswered(cur
						.getInt(QUESTION_FIELDS.TIMES_ANSWERED.ordinal()));
				question.setShowDate(new Date(cur
						.getLong(QUESTION_FIELDS.SHOW_DATE.ordinal())));
				questions.add(question);
			} while (cur.moveToNext());
		}
		cur.close();
		return questions;
	}

	public void resetQuestions(List<Question> questions) {
		beginTransaction();
		for (Question question : questions) {
			question.setShowDate(null);
			question.setTimesAnswered(0);
			updateQuestion(question);
		}
		endTransaction();
	}

	public void resetQuestions(Chapter chapter) {
		List<Question> questions = getAllQuestionsFromChapter(chapter
				.getChapterKey());
		resetQuestions(questions);
	}

	private static class OpenHelper extends SQLiteOpenHelper {

		public OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			String sql = "create table " + BOOK_TABLE + "( "
					+ BOOK_FIELDS.ID.getName() + " text primary key, "
					+ BOOK_FIELDS.BOOK_TITLE.getName() + " text not null, "
					+ BOOK_FIELDS.BOOK_AUTHOR_NAME.getName() + " text,"
					+ BOOK_FIELDS.BOOK_AUTHOR_EMAIL.getName() + " text,"
					+ BOOK_FIELDS.IS_DOCTOR.getName() + " integer, "
					+ BOOK_FIELDS.INSTITUTION.getName() + " text)";
			Log.d("DBHelper", "onCreate: " + sql);
			db.execSQL(sql);

			sql = "create table " + CHAPTER_TABLE + "( "
					+ CHAPTER_FIELDS.ID.getName() + " text primary key, "
					+ CHAPTER_FIELDS.BOOK_KEY.getName() + " text not null, "
					+ CHAPTER_FIELDS.CHAPTER_TITLE.getName()
					+ " text not null, "
					+ CHAPTER_FIELDS.CHAPTER_AUTHOR_NAME.getName() + " text, "
					+ CHAPTER_FIELDS.CHAPTER_AUTHOR_EMAIL.getName() + " text, "
					+ CHAPTER_FIELDS.IS_DOCTOR.getName() + " integer, "
					+ CHAPTER_FIELDS.INSTITUTION.getName() + " text, "
					+ CHAPTER_FIELDS.CHAPTER_VERSION.getName() + " number, "
					+ CHAPTER_FIELDS.ALLOW_PROP.getName() + " text, "
					+ "FOREIGN KEY ("
					+ CHAPTER_FIELDS.BOOK_KEY.getName() + ") REFERENCES "
					+ BOOK_TABLE + " (" + CHAPTER_FIELDS.ID.getName() + "))";
			Log.d("DBHelper", "onCreate: " + sql);
			db.execSQL(sql);

			sql = "create table " + QUESTION_TABLE + "( "
					+ QUESTION_FIELDS.ID.getName()
					+ " integer primary key autoincrement, "
					+ QUESTION_FIELDS.CHAPTER_KEY.getName()
					+ " text not null, " + QUESTION_FIELDS.QUESTION.getName()
					+ " text not null, " + QUESTION_FIELDS.ANSWER.getName()
					+ " text not null, "
					+ QUESTION_FIELDS.TIMES_ANSWERED.getName()
					+ " integer not null, "
					+ QUESTION_FIELDS.SHOW_DATE.getName()
					+ " number, FOREIGN KEY ("
					+ QUESTION_FIELDS.CHAPTER_KEY.getName() + ") REFERENCES "
					+ CHAPTER_TABLE + " (" + CHAPTER_FIELDS.ID.getName() + "))";
			Log.d("DBHelper", "onCreate: " + sql);
			db.execSQL(sql);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
		}
	}

	public void close() {
		if (database != null) {
			database.close();
		}
	}

	public boolean doesDatabaseExist() {
		SQLiteDatabase checkDatabase = null;

		try {
			checkDatabase = SQLiteDatabase.openDatabase(FULL_PATH, null,
					SQLiteDatabase.OPEN_READONLY);
			checkDatabase.close();
		} catch (Exception e) {
			// database doesn't exist.
		}
		if (checkDatabase != null) {
			Log.d("Database exists.", DATABASE_NAME);
			return true;
		} else {
			Log.d("Database doesn't exist.", DATABASE_NAME);
			return false;
		}
	}
}
